<?php

namespace longbingcore\wxcore;

class Excel
{
    public function readExcel($filename)
    {
        require_once EXTEND_PATH . "PHPExcel/PHPExcel.php";
        if(empty($filename) || !is_file($filename)) {
            return "该文件错误";
        }
        $objReader = \PHPExcel_IOFactory::createReaderForFile($filename);
        $objPHPExcel = $objReader->load($filename);
        $objPHPExcel->setActiveSheetIndex(0);
        $sheet = $objPHPExcel->getSheet(0);
        $highestRow = $sheet->getHighestRow();
        $highestColumn = $sheet->getHighestColumn();
        for ($row = 2; $row <= $highestRow; $row++) {
            $rowData = $sheet->rangeToArray("A" . $row . ":" . $highestColumn . $row, NULL, true, false);
            $data[] = $rowData;
        }
        return $data;
    }
    public function excelExport($fileName = "", $headArr = [], $data = [], $type = "", $status = 0)
    {
        require_once EXTEND_PATH . "PHPExcel/PHPExcel.php";
        ini_set("memory_limit", "1024M");
        set_time_limit(30);
        $fileName .= ".xls";
        $objPHPExcel = new \PHPExcel();
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, "Excel5");
        $objPHPExcel->getProperties();
        $key = ord("A");
        $key2 = ord("@");
        foreach ($headArr as $v) {
            $e = mb_detect_encoding($v, ["UTF-8", "GBK"]);
            if($e != "UTF-8") {
                $v = mb_convert_encoding($v, "UTF-8", "GBK");
            }
            if(ord("Z") < $key) {
                $colum = chr(ord("A")) . chr(++$key2);
            } else {
                $colum = chr($key++);
            }
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($colum . "1", $v);
        }
        $column = 2;
        $objActSheet = $objPHPExcel->getActiveSheet(0);
        if($type == 1) {
            $this->mergeCard($objPHPExcel);
        }
        $fileName = iconv("utf-8", "GBK", $fileName);
        foreach ($data as $key => $rows) {
            $span = ord("A");
            $key2 = ord("@");
            foreach ($rows as $keyName => $value) {
                if(ord("Z") < $span) {
                    $colum = chr(ord("A")) . chr(++$key2);
                } else {
                    $colum = chr($span);
                }
                if(!empty($value) && is_string($value)) {
                    $e = mb_detect_encoding($value, ["UTF-8", "GBK"]);
                    $value = $this->filterEmoji($value);
                    if($e != "UTF-8") {
                        $value = mb_convert_encoding($value, "UTF-8", "GBK");
                    }
                }
                $objActSheet->setCellValueExplicit($colum . $column, $value);
                $span++;
            }
            $column++;
        }
        if(in_array($status, [1, 2, 3])) {
            $this->mergeOrderExcel($objPHPExcel, $data, $status);
        }
        ob_end_clean();
        header("Content-Type: application/vnd.ms-excel");
        header("Content-Disposition: attachment;filename=" . $fileName);
        header("Cache-Control: max-age=0");
        header("content-Type:application/vnd.ms-excel;charset=utf-8");
        $objWriter->save("php://output");
        return $fileName;
    }
    public function excelCsv($fileName, $header, $result, $type = "", $status = 0)
    {
        set_time_limit(0);
        ini_set("memory_limit", "1024M");
        header("Content-Encoding: UTF-8");
        header("Content-type:application/vnd.ms-excel;charset=UTF-8");
        header("Content-Disposition: attachment;filename=\"" . $fileName . ".csv\"");
        $fp = fopen("php://output", "a");
        fwrite($fp, chr(239) . chr(187) . chr(191));
        fputcsv($fp, $header);
        ob_start();
        foreach ($result as $item) {
            fputcsv($fp, $item);
        }
        ob_flush();
        flush();
    }
    public function mergeOrderExcel($obj, $data, $status = 1)
    {
        foreach ($data as $k => $v) {
            if(!empty($v)) {
                $v["key"] = $k + 2;
                $newdata[$v[0]][] = $v;
            }
        }
        switch ($status) {
            case 1:
                $arr = ["A", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y"];
                break;
            case 2:
                $arr = ["A", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X"];
                break;
            case 3:
                $arr = ["A", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V"];
                break;
            default:

        }
        if(!empty($newdata)) {
            foreach ($newdata as $k => $v) {
                if(1 < count($v)) {
                    $count = count($v) - 1;
                    foreach ($arr as $value) {
                        $me = $value . $v[0]["key"] . ":" . $value . $v[$count]["key"];
                        $obj->getActiveSheet()->mergeCells($me);
                    }
                }
            }
        }
    }
    public function filterEmoji($str)
    {
        $str = preg_replace_callback("/./u", function (array $match) {
            return 4 <= strlen($match[0]) ? "" : $match[0];
        }, $str);
        return $str;
    }
    public function mergeCard($objPHPExcel)
    {
        $arr = ["A", "B", "C", "D"];
        foreach ($arr as $value) {
            $s_icon = $value . "1" . ":" . $value . "2";
            $h_icon = $value . "1";
            $objPHPExcel->getActiveSheet()->mergeCells($s_icon);
            $objPHPExcel->getActiveSheet()->getstyle($s_icon)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle($h_icon)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        }
        $arr2 = ["E1:F1", "G1:H1", "I1:J1", "K1:L1", "M1:N1", "O1:P1"];
        foreach ($arr2 as $value) {
            $objPHPExcel->getActiveSheet()->mergeCells($value);
            $objPHPExcel->getActiveSheet()->getstyle($value)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle($value)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        }
        $arr3 = ["E2", "F2", "G2", "H2", "I2", "J2", "K2", "L2", "M2", "N2", "O2", "P2"];
        foreach ($arr3 as $value) {
            $objPHPExcel->getActiveSheet()->getStyle($value)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        }
        return true;
    }
}

?>